***********************************************************************
******
****** AKMP_DHS_WealthDecoder.do
******
****** Recode the DHS aggregate wealth data into variables that can be used in 
****** an analysis of household wealth, savings, and investments. 
******
****** CURRENT VERSION:
****** Created  on 21 Jan 2016; by Roy Kouwenberg
****** Modified on 25 Jan 2018 (updated for 2018 DHS data)
****** Modified on  7 Feb 2024 (cleaned up for replication code)
******
****** Input file  : agw2018en_1.0.dta, the DHS 2008 aggregate wealth dataset 
******
****** Output files: wealth2018_decoded.dta,  full version of the decoded 2018 DHS household wealth data
****** 				 wealth2018_summary.dta,  smaller version with only the relevant variables for the article
******
****** List of variables created: 
******         
****** Note: the program also constructs the same variables with "hh" at the end 
****** of the name, for household wealth (summed over all household members)
******
******         Financial assets:
******
******		   savings      = savings amount (non-negative) in euro
******		   insurance    = cash value of insurance policies in euro
******		   bonds        = bonds amount in euro 
******		   mutualfunds  = mutual fund amount in euro (bond funds, growth funds, equity funds, mix funds)
******		   stocks       = stocks amount in euro (publicly traded, no funds, no private business)
******		   options      = options amount in euro
******		   finother     = other financial assets ammount in euro (loans to friends/family, other investments/savings)
******         finass       = total fiancial assets amount (sum of the above)
******         dfinass1000  = dummy for financial assets of 1000 euro or more
******         dfinasspos   = dummy for financial assets positive dummy: 
******		   bondsorfunds = bonds  + mutualfunds
******		   equity       = stocks + mutualfunds
******         dbonds       = dummy for bond ownership
******         dmutualfunds = dummy for mutual fund ownership
******         dstocks      = dummy for stock ownership
******         doptions     = dummy for options ownership
******         dfinother    = dummy for other fin. assets ownership 
******         dbondsorfunds= dummy for bonds or mutual fund ownership
******         dequity      = dummy for equity ownership (stocks or mutual funds)
******
******         Financial asset allocation:
******
******         wsavings     = savings weight in financial assets
******         winsurance   = insurance weight in financial assets
******         wbonds       = bonds weight in financial assets
******         wmutualfunds = mutual funds weight in financial assets
******         wstocks      = stocks weight in financial assets
******         woptions     = options weight in financial assets
******         wfinother    = other fin. assets weight in financial assets
******		   wbondsorfunds= bonds and mutual funds weight in financial assets
******		   wequity      = equity weight in financial assets
******
******         Real assets:
******
******         realass_housing = value of housing (1st and 2nd residence, and other real estate)
******         realass_other   = value cars, motorbikes, boats and caravans
******         privbus         = value of private businesses (BV holding company, partnerships, own company)
******         dhouse_ms       = dummy flagging missing housing wealth data
******         Note: if data from "Accommodation and Mortgages" was missing on questions b26 and b27 (1st and 2nd house), it was replaced by zero 
******
******         Total assets:
******
******         totass = finass + privbus + realass_other + realass_housing 
******         wtfinass    = Financial assets weight in total assets
******         wtprivbus   = Private business weight in total assets
******         wthousing   = Housing weight in total assets
******         wtrealother = Other real assets weight in total assets
******
******         Debt:
******
******         debt_unsecured = unsecured debt amount in euros 
******         debt_housing   = mortgage debt amount in euros
******         totdebt        = total debt in euros (debt_unsecured + debt_housing)
******         Note: debt_unsecured includes negative total amounts in checking accounts, which was exclused from savings and financial assets
******
******         Total net wealth:
******
******         netwealth      = Total net wealth in euros (totass - totdebt)
******

***********************************************************************
****** Set the working directory and open the 2018 DHS wealth datafile
***********************************************************************

* Set the working directory 
cd "D:\Mydocs\Papers\AKMP\Replication\Data"

* Open the raw data file, DHS aggregate wealth 2018
use "agw2018en_1.0.dta", clear


***********************************************************************
****** Process the data and create new variables
***********************************************************************

* Create a unique DHS panel member identifier: noid
set more off
gen noid = 100*nohhold + nomem
gen year = 2008
label variable noid "DHS member identifier"
sort noid

* Create financial asset variables
*
* 1. Savings
* 2. Cash value of insurance 
* 3. Bonds
* 4. Mutual funds 
* 5. Stocks
* 6. Options 
* 7. Other financial assets 
*

* 1. Savings amount components
*
* Sum of the following component amounts
* b1b: Checking accounts
* b2b: Employer-sponsored savings plans
* b3b: Savings arrangements linked to a postbank account
* b4b: Deposit books	
* b6b: Saving certificats	
* x1b: total amount checking accounts with positive/negative deficit balance
*
*
* Note available in 2005 and 2017 (discarded)
*      b5b: Savings or deposit accounts	
* 
summ b1b b2b b3b b4b b6b x1b

* Savings amount variable: savings
*
* Note: savings calculated without b5b, which is missing in 2005
*
* Note: total checking accounts amount "b1b" can be a negative. 
*       To separately track assets and debt, we add back the "negative checking accounts amounts" with the variable "x1b" (part of unsecured debt): b1bpos
*       Six values that remain negative are reset to zero.
*
gen b1bpos  = b1b + x1b 
replace b1bpos  = 0   if b1bpos  < 0
gen savings = b1bpos + b2b + b3b + b4b + b6b
label variable savings "Savings amount in euro"
summ savings
summ savings, detail
hist savings

* Savings positive dummy: dsavpos
*
gen dsavpos = 0 if savings <= 0
replace dsavpos = 1 if savings > 0 & savings  < .
label variable dsavpos "Dummy savings positive"
summ dsavpos savings 
summ savings if dsavpos == 0
summ savings if dsavpos == 1

* Aggregate savings at the household level
sort nohhold
by nohhold: egen savingshh =total(savings)     
label variable savingshh "Household savings in euro"

* Savings at household level positive dummy: dsavhhpos
*
gen dsavhhpos = 0 if savingshh <= 0
replace dsavhhpos = 1 if savingshh > 0 & savingshh  < .
label variable dsavhhpos "Dummy household savings positive"
summ dsavhhpos savingshh dsavpos savings
summ savingshh dsavpos savings if dsavhhpos == 0
summ savingshh dsavpos savings if dsavhhpos == 1



* 2. Cash value of insurance components	
*
* b7b: Single-premium annuity insurance policies	
* b8b: Savings or endowment insurance policies
* b19vzb: cash value life insurance on mortgage real estate not being used as own accomodation (not 1st or 2nd residence, see below)
* b26vzb: cash value life insurance of mortgage on 1st residence
* b27vzb: cash value life insurance of mortgage on 2nd residence
*
* Note b26vzb and b27vzb are from questionnaire Accommodation and Mortgages, and have lower number of observations
* 
summ b7b b8b b19vzb b26vzb b27vzb


* Cash value of insurance: insurance
*
* insurance0: Insurance cash value in euro, excluding life insurance that is part of the mortgage on primary residences (1st house, 2nd house)
* insurance : Insurance cash value in euro, inclusive life insurance that is part of the mortgage on primary residences (1st house, 2nd house)
*
gen insurance0 = b7b + b8b + b19vzb 
label variable insurance0 "Insurance cash value in euro, ex mortgage life insurance on residences"
gen insurance = insurance0
gen b26vzb_nm = b26vzb
replace b26vzb_nm = 0 if b26vzb == .
gen b27vzb_nm = b27vzb
replace b27vzb_nm = 0 if b27vzb == .
replace insurance = insurance0 + b26vzb_nm + b27vzb_nm
label variable insurance "Insurance cash value in euro, inc mortgage life insurance on residences"
summ insurance0 insurance
summ insurance0 insurance, detail
hist insurance


* Cash value of insurance at household level: insurancehh
*
* insurancehh0: Insurance cash value in euro, excluding life insurance that is part of the mortgage on primary residences (1st house, 2nd house)
* insurancehh : Insurance cash value in euro, inclusive life insurance that is part of the mortgage on primary residences (1st house, 2nd house)
*
by nohhold: egen insurancehh =total(insurance)     
by nohhold: egen insurance0hh =total(insurance0)     
label variable insurancehh "Insurance cash value at hh-level in euro, inc mortgage life insurance on residences"
label variable insurance0hh "Insurance cash value at hh-level in euro, exc mortgage life insurance on residences"



* 3. Mutual funds components	
*
* b11b: growth funds
* b12b: mutual funds and/or mutual fund accounts 
*
* Note: growth funds are mutual funds that reinvest interest/dividends, populare before 2001 because of tax reasons
* http://www.morningstar.nl/nl/glossary/102345/groeifonds.aspx
* 
summ b11b b12b

* Mutual funds amount variable: mutualfunds
*
gen mutualfunds = b11b + b12b
label variable mutualfunds "Mutual funds amount in euro"
summ mutualfunds
summ mutualfunds, detail
hist mutualfunds

* Mutual funds ownership dummy: dmutualfunds
*
gen dmutualfunds = 0 if mutualfunds == 0
replace dmutualfunds = 1 if mutualfunds > 0 & mutualfunds < .
label variable dmutualfunds "Dummy mutual fund ownership"
summ dmutualfunds mutualfunds
summ mutualfunds if dmutualfunds == 0
summ mutualfunds if dmutualfunds == 1

* Mutual funds amount at the hh-level: mutualfundshh
by nohhold: egen mutualfundshh =total(mutualfunds)     
label variable mutualfundshh "Mutual funds amount in euro at hh-level"

* Dummy for mutual funds ownership at the hh-level: dmutualfundshh
*
gen dmutualfundshh = 0 if mutualfundshh == 0
replace dmutualfundshh = 1 if mutualfundshh > 0 & mutualfundshh < .
label variable dmutualfundshh "Dummy hh mutual fund ownership"
summ dmutualfundshh mutualfundshh
summ mutualfundshh if dmutualfundshh == 0
summ mutualfundshh if dmutualfundshh == 1



* 4. Bonds components	
*
* b13b: bonds and/or mortgage bonds
* 
summ b13b

* Bonds amount variable: bonds
*
gen bonds = b13b
label variable bonds "Bonds amount in euro"
summ bonds
summ bonds, detail
hist bonds

* Bonds ownership dummy: dbonds
*
gen dbonds = 0 if bonds == 0
replace dbonds = 1 if bonds > 0 & bonds < .
label variable dbonds "Dummy bonds ownership"
summ dbonds bonds
summ bonds if dbonds == 0
summ bonds if dbonds == 1

* Bonds amount at the hh-level: bondshh
by nohhold: egen bondshh =total(bonds)     
label variable bondshh "Bonds amount in euro at hh-level"

* Dummy for stocks ownership at the hh-level: dbondshh
*
gen dbondshh = 0 if bondshh == 0
replace dbondshh = 1 if bondshh > 0 & bondshh < .
label variable dbondshh "Dummy hh bonds ownership"
summ dbondshh bondshh bonds 
summ bondshh bonds if dbondshh == 0
summ bondshh bonds if dbondshh == 1


* 5. Stocks components	
*
* b14b: stocks, public (excluding private businesses)
* 
summ b14b

* Stocks amount variable: stocks
*
gen stocks = b14b
label variable stocks "Stocks amount in euro"
summ stocks
summ stocks, detail
hist stocks

* Stocks ownership dummy: dstocks
*
gen dstocks = 0 if stocks == 0
replace dstocks = 1 if stocks > 0 & stocks < .
label variable dstocks "Dummy stock ownership"
summ dstocks stocks
summ stocks if dstocks == 0
summ stocks if dstocks == 1

* Stocks amount at the hh-level: stockshh
by nohhold: egen stockshh =total(stocks)     
label variable stockshh "Stocks amount in euro at hh-level"

* Dummy for stocks ownership at the hh-level: dstockshh
*
gen dstockshh = 0 if stockshh == 0
replace dstockshh = 1 if stockshh > 0 & stockshh < .
label variable dstockshh "Dummy hh stocks ownership"
summ dstockshh stockshh dstocks stocks 
summ stockshh dstocks stocks if dstockshh == 0
summ stockshh dstocks stocks if dstockshh == 1




* 6. Options components	
*
* b15b: put-options bought
* b16b: put-options written
* b17b: call-options bought
* b18b: call-options written
* 
summ b15b b16b b17b b18b

* Options amount variable: options
*
gen options = b15b + b16b + b17b + b18b
label variable options "Options amount in euro"
summ options
summ options, detail
hist options

* Options ownership dummy: doptions
*
gen doptions = 0 if options == 0
replace doptions = 1 if options > 0 & options < .
label variable doptions "Dummy options ownership"
summ doptions options
summ options if doptions == 0
summ options if doptions == 1

* Options amount at the hh-level: optionshh
by nohhold: egen optionshh =total(options)     
label variable optionshh "Options amount in euro at hh-level"

* Dummy for options ownership at the hh-level: doptionshh
*
gen doptionshh = 0 if optionshh == 0
replace doptionshh = 1 if optionshh > 0 & optionshh < .
label variable doptionshh "Dummy hh options ownership"
summ doptionshh doptions optionshh options 
summ optionshh doptions options if doptionshh == 0
summ optionshh doptions options if doptionshh == 1




* 7. Other financial assets components	
*
* b24b: money lent out to family or friends (1=yes, 0=no)
* b25b: savings or investments not mentioned before
* 
summ b24b b25b

* Other financial assets variable: finother
*
gen finother = b24b + b25b
label variable finother "Other financial assets amount in euro (loans to friend, and other savings/investments)"
summ finother
summ finother, detail
hist finother

* Other financial assets ownership dummy: dfinother
*
gen dfinother = 0 if finother == 0
replace dfinother = 1 if finother > 0 & finother < .
label variable dfinother "Dummy other financial assets ownership (loans to friend, and other savings/investments)"
summ dfinother finother
summ finother if dfinother == 0
summ finother if dfinother == 1

* Other financial assets amount at the hh-level: finotherhh
by nohhold: egen finotherhh =total(finother)     
label variable finotherhh "Other financial assets in euro at hh-level"

* Dummy for other financial assets ownership at the hh-level: dfinotherhh
*
gen dfinotherhh = 0 if finotherhh == 0
replace dfinotherhh = 1 if finotherhh > 0 & finotherhh < .
label variable dfinotherhh "Dummy hh other financial assets ownership"
summ dfinotherhh dfinother finotherhh finother 
summ finotherhh dfinother finother if dfinotherhh == 0
summ finotherhh dfinother finother if dfinotherhh == 1



* Bonds or mutual funds: combined category
*
* bondsorfunds = bonds + mutualfunds
*
gen bondsorfunds = bonds + mutualfunds
label variable bondsorfunds "Bonds and mutual funds combined amount in euro"
gen dbondsorfunds = dbonds + dmutualfunds
replace dbondsorfunds = 1 if dbondsorfunds == 2
label variable dbondsorfunds "Dummy for bonds or mutual fund ownership"
summ bondsorfunds bonds mutualfunds dbondsorfunds dbonds dmutualfunds
summ bondsorfunds bonds mutualfunds dbondsorfunds dbonds dmutualfunds if dbondsorfunds == 0
summ bondsorfunds bonds mutualfunds dbondsorfunds dbonds dmutualfunds if dbondsorfunds == 1
correlate bondsorfunds bonds mutualfunds dbondsorfunds dbonds dmutualfunds

by nohhold: egen bondsorfundshh =total(bondsorfunds)     
label variable bondsorfundshh "Bonds and mutual funds combined amount in euro at hh-level"
gen dbondsorfundshh = 0 if bondsorfundshh == 0
replace dbondsorfundshh = 1 if bondsorfundshh > 0 & bondsorfundshh < .
label variable dbondsorfundshh "Dummy hh bondsorfunds ownership"
summ dbondsorfundshh dbondsorfunds bondsorfundshh bondsorfunds 
summ bondsorfundshh dbondsorfunds bondsorfunds if dbondsorfundshh == 0
summ bondsorfundshh dbondsorfunds bondsorfunds if dbondsorfundshh == 1




* Equity : combined category
*
* equity = stocks + mutualfunds
*
* 
summ b14b b11b b12b
gen equity = stocks + mutualfunds
label variable equity "Equity amount in euro (stocks or mutual funds)"
summ equity
summ equity, detail
hist equity
gen dequity = 0 if equity == 0
replace dequity = 1 if equity > 0 & equity < .
label variable dequity "Dummy equity ownership (stocks or mutual funds)"
summ dequity dmutualfunds dstocks equity mutualfunds stocks
summ equity mutualfunds stocks if dequity == 0
summ equity mutualfunds stocks if dequity == 1

by nohhold: egen equityhh =total(equity)     
label variable equityhh "Equity amount in euro at hh-level"
gen dequityhh = 0 if equityhh == 0
replace dequityhh = 1 if equityhh > 0 & equityhh < .
label variable dequityhh "Dummy hh equity ownership (stocks or mutual funds)"
summ dequityhh dequity equityhh equity 
summ equityhh dequity equity if dequityhh == 0
summ equityhh dequity equity if dequityhh == 1




* Create total financial assets variable
* 
* Financial assets components
*
* 1. Savings
* b1bpos: Checking accounts, non-negative amounts
* b2b: Employer-sponsored savings plans
* b3b: Savings arrangements linked to a postbank account
* b4b: Deposit books	
* b6b: Saving certificats	
*
* 2. Cash value of insurance 
* b7b: Single-premium annuity insurance policies	
* b8b: Savings or endowment insurance policies
* b19vzb: cash value life insurance on mortgage real estate not being used as own accomodation (not 1st or 2nd residence, see below)
* b26vzb: cash value life insurance of mortgage on 1st residence
* b27vzb: cash value life insurance of mortgage on 2nd residence
*
* 3. Bonds
* b13b: bonds and/or mortgage bonds
* 
* 4. Mutual funds 
* b11b: growth funds
* b12b: mutual funds and/or mutual fund accounts 
* 
* 5. Stocks
* b14b: stocks, public (excluding private businesses)
* 
* 6. Options 
* b15b: put-options bought
* b16b: put-options written
* b17b: call-options bought
* b18b: call-options written
* 
* 7. Other financial assets 
* b24b: money lent out to family or friends (1=yes, 0=no)
* b25b: savings or investments not mentioned before
*
summ b1bpos b2b b3b b4b b6b b7b b8b b19vzb b26vzb_nm b27vzb_nm b13b b11b b12b b14b b15b b16b b17b b18b b24b b25b
gen finass = savings + insurance + bonds + mutualfunds + stocks + options + finother
label variable finass "Financial assets amount in euro"
summ finass
summ finass, detail
hist finass

* Check the calculation/definition of financial assets
gen fincheck = b1bpos + b2b + b3b + b4b + b6b + b7b + b8b + b19vzb + b26vzb_nm + b27vzb_nm + b13b + b11b + b12b + b14b + b15b + b16b + b17b + b18b + b24b + b25b
summ finass fincheck

* Financial assets positive dummy: dfinasspos
*
gen dfinasspos = 0 if finass <= 0
replace dfinasspos = 1 if finass > 0 & finass < .
label variable dfinasspos "Dummy financial assets positive"
summ dfinasspos finass
summ finass if dfinasspos == 0
summ finass if dfinasspos == 1

* Financial assets of 1000 euro or more: dfinass1000
*
gen dfinass1000 = 0 if finass < 1000
replace dfinass1000 = 1 if finass >= 1000 & finass < .
label variable dfinass1000 "Dummy finass >= 1000 euro"
summ dfinass1000 finass
summ finass if dfinass1000 == 0
summ finass if dfinass1000 == 1

* Financial assets at the household level: finasshh
*
by nohhold: egen finasshh =total(finass)     
label variable finasshh "Financial assets amount in euro at hh-level"

* Check the calculation/definition of household financial assets
gen fincheckhh = savingshh + insurancehh + bondshh + mutualfundshh + stockshh + optionshh + finotherhh
summ finasshh fincheckhh

* Household financial assets positive dummy: dfinasshhpos
*
gen dfinasshhpos = 0 if finasshh <= 0
replace dfinasshhpos = 1 if finasshh > 0 & finasshh < .
label variable dfinasshhpos "Dummy household financial assets positive"
summ dfinasshhpos finasshh
summ finasshh if dfinasshhpos == 0
summ finasshh if dfinasshhpos == 1

* Household financial assets of 1000 euro or more: dfinasshh1000
*
gen dfinasshh1000 = 0 if finasshh < 1000
replace dfinasshh1000 = 1 if finasshh >= 1000 & finasshh < .
label variable dfinasshh1000 "Dummy household finass >= 1000 euro"
summ dfinasshh1000 finasshh
summ finasshh if dfinasshh1000 == 0
summ finasshh if dfinasshh1000 == 1





* Generate fraction of financial invested in savings, insurance, bonds, mutual funds, stocks, options and other.
*
* Note: only for those people with positive financial asset amounts, otherwise missing. 
* 
gen wsavings     = savings/finass     if dfinasspos == 1
gen winsurance   = insurance/finass   if dfinasspos == 1
gen wbonds       = bonds/finass       if dfinasspos == 1 
gen wmutualfunds = mutualfunds/finass if dfinasspos == 1
gen wstocks      = stocks/finass      if dfinasspos == 1
gen woptions     = options/finass     if dfinasspos == 1
gen wfinother    = finother/finass    if dfinasspos == 1
label variable wsavings "Savings weight in financial assets"
label variable winsurance "Insurance weight in financial assets"
label variable wbonds "Bonds weight in financial assets"
label variable wmutualfunds "Mutual funds weight in financial assets"
label variable wstocks "Stocks weight in financial assets"
label variable woptions "Otions weight in financial assets"
label variable wfinother "Other fin. assets weight in financial assets"
gen wchecksum = wsavings + winsurance + wbonds + wmutualfunds + wstocks + woptions + wfinother
sum wsavings winsurance wbonds wmutualfunds wstocks woptions wfinother wchecksum 

 
* Other fractions: equity, and bondsormutualfunds
gen wequity       = equity/finass       if dfinasspos == 1 
gen wbondsorfunds = bondsorfunds/finass if dfinasspos == 1
label variable wequity "Equity weight in financial assets"
label variable wbondsorfunds "Bonds and mutual funds weight in financial assets"
sum wbonds wmutualfunds wstocks wbondsorfunds wequity      


* Generate fraction of household financial invested in savings, insurance, bonds, mutual funds, stocks, options and other.
*
* Note: only for households with positive financial asset amounts, otherwise missing. 
* 
gen wsavingshh     = savingshh/finasshh     if dfinasshhpos == 1
gen winsurancehh   = insurancehh/finasshh   if dfinasshhpos == 1
gen wbondshh       = bondshh/finasshh       if dfinasshhpos == 1 
gen wmutualfundshh = mutualfundshh/finasshh if dfinasshhpos == 1
gen wstockshh      = stockshh/finasshh      if dfinasshhpos == 1
gen woptionshh     = optionshh/finasshh     if dfinasshhpos == 1
gen wfinotherhh    = finotherhh/finasshh    if dfinasshhpos == 1
label variable wsavingshh "Savings weight in household financial assets"
label variable winsurancehh "Insurance weight in household financial assets"
label variable wbondshh "Bonds weight in household financial assets"
label variable wmutualfundshh "Mutual funds weight in household financial assets"
label variable wstockshh "Stocks weight in household financial assets"
label variable woptionshh "Otions weight in household financial assets"
label variable wfinotherhh "Other fin. assets weight in household financial assets"
gen wchecksumhh = wsavingshh + winsurancehh + wbondshh + wmutualfundshh + wstockshh + woptionshh + wfinotherhh
sum wsavingshh winsurancehh wbondshh wmutualfundshh wstockshh woptionshh wfinotherhh wchecksumhh 

 
* Other household fractions: equity, and bondsormutualfunds
gen wequityhh       = equityhh/finasshh       if dfinasshhpos == 1 
gen wbondsorfundshh = bondsorfundshh/finasshh if dfinasshhpos == 1
label variable wequityhh "Equity weight in household financial assets"
label variable wbondsorfundshh "Bonds and mutual funds weight in household financial assets"
sum wbondshh wmutualfundshh wstockshh wbondsorfundshh wequityhh    



* These are all the financial asset items
summ b1bpos b2b b3b b4b b6b b7b b8b b11b b12b b13b b14b b15b b16b b17b b18b b19vzb b24b b25b b26vzb_nm b27vzb_nm 

* Empty items: b5b, b9b, b10b
* summ b5b b9b b10b

* Real assets, non-housing: b20b b21b b22b b23b
summ b20b b21b b22b b23b

* Private business: b28b b29b b30b
*
summ b28b b29b b30b


* Create real asset variables
*
*  8. Real assets, non-housing (cars, motorbikes, boats and caravans)
*  9. Real assets, housing
* 10. Private business
*


* 8. Real assets, non-housing (cars, motorbikes, boats and caravans)
*
* b20b: cars
* b21b: motorbikes
* b22b: boats
* b23b: caravans/trailers
*
summ b20b b21b b22b b23b
gen realass_other = b20b + b21b + b22b + b23b
label variable realass_other "Other real assets in euros (cars, motorbikes, boats and caravans)"
by nohhold: egen realasshh_other =total(realass_other)     
label variable realasshh_other "Other real household assets in euros (cars, motorbikes, boats and caravans)"
summ realass_other realasshh_other
 
* 9. Real assets, housing (1st residence, 2nd residence and investment real estate)
*
* b19ogb: value of real estate not being used as own accomodation (not 1st or 2nd residence, see below)
* b26ogb: value of 1st residence
* b27ogb: value of 2nd residence
*
summ b19ogb b26ogb b27ogb 
gen b26ogb_nm = b26ogb
replace b26ogb_nm = 0 if b26ogb == .
gen b27ogb_nm = b27ogb
replace b27ogb_nm = 0 if b27ogb == .
gen realass_housing = b19ogb + b26ogb_nm + b27ogb_nm
label variable realass_housing "Housing assets in euros, including 1st/2nd residence"
summ realass_housing 
by nohhold: egen realasshh_housing =total(realass_housing)     
label variable realasshh_housing "Household housing assets in euros, including 1st/2nd residence"
summ realass_housing realasshh_housing


* Create a dummy flagging missing housing wealth data: dhouse_ms 
*
* Note: if data from "Accommodation and Mortgages" was missing on questions b26 and b27 (1st and 2nd house), it was replaced by zero 
* So we need to add this missing data dummy later in regressions
*
gen dhouse_ms = 0 if realass_housing < .
replace dhouse_ms = 1 if b26ogb == . & realass_housing < .
replace dhouse_ms = 1 if b27ogb == . & realass_housing < .
label variable dhouse_ms "Missing housing data on residences (1st/2nd, inclusing mortgages)"
summ dhouse_ms realass_housing b19ogb b26ogb b27ogb 
summ dhouse_ms realass_housing b19ogb b26ogb b27ogb if dhouse_ms == 0
summ dhouse_ms realass_housing b19ogb b26ogb b27ogb if dhouse_ms == 1


* 10. Private business components	
*
* b28b: stocks from substantial holding (NL = BV)
* b29b: business equity (professions, NL = maatschap, vennootschap)
* b30b: business equity self-employed (NL = zelfstandige)
* 
label variable b29b "business equity (professions)"
summ b28b b29b b30b

* Private business amount variable: privbus
*
gen privbus = b28b + b29b + b30b
label variable privbus "Private business amount in euro"
summ privbus
summ privbus, detail
hist privbus
by nohhold: egen privbushh =total(privbus)     
label variable privbushh "Household private business amount in euro"
summ privbus privbushh

* Private business ownership dummy: dprivbus
*
gen dprivbus = 0 if privbus == 0
replace dprivbus = 1 if privbus > 0 & privbus < .
label variable dprivbus "Dummy private business ownership"
summ dprivbus privbus
summ privbus if dprivbus == 0
summ privbus if dprivbus == 1

gen dprivbushh = 0 if privbushh == 0
replace dprivbushh = 1 if privbushh > 0 & privbushh < .
label variable dprivbushh "Dummy household private business ownership"
summ dprivbushh privbushh
summ privbushh if dprivbushh == 0
summ privbushh if dprivbushh == 1




* Total assets variable
*
* totass = finass + privbus + realass_other + realass_housing 
*  
* 1-7: Financial assets  
*   8: Real assets, non-housing (cars, motorbikes, boats and caravans)
*   9: Real assets, housing
*  10: Private business
*
gen totass = finass + privbus + realass_other + realass_housing 
label variable totass "Total assets in euros"
summ totass finass realass_other realass_housing privbus
summ totass finass realass_other realass_housing privbus if dhouse_ms == 0
summ totass finass realass_other realass_housing privbus if dhouse_ms == 1
by nohhold: egen totasshh =total(totass)     
label variable totasshh "Total household assets amount in euro"
gen check_totasshh = finasshh + privbushh + realasshh_other + realasshh_housing 
summ totass totasshh check_totasshh


* Total assets positive dummy: dtotasspos
*
gen dtotasspos = 0 if totass <= 0
replace dtotasspos = 1 if totass > 0 & totass < .
label variable dtotasspos "Dummy total assets positive"
summ dtotasspos totass
summ totass if dtotasspos == 0
summ totass if dtotasspos == 1

gen dtotasshhpos = 0 if totasshh <= 0
replace dtotasshhpos = 1 if totasshh > 0 & totasshh < .
label variable dtotasshhpos "Dummy total household assets  positive"
summ dtotasshhpos totasshh
summ totasshh if dtotasshhpos == 0
summ totasshh if dtotasshhpos == 1


* Total assets of 1000 euro or more: dtotass1000
*
gen dtotass1000 = 0 if totass < 1000
replace dtotass1000 = 1 if totass >= 1000 & totass < .
label variable dtotass1000 "Dummy totass >= 1000 euro"
summ dtotass1000 totass
summ totass if dtotass1000 == 0
summ totass if dtotass1000 == 1

gen dtotasshh1000 = 0 if totasshh < 1000
replace dtotasshh1000 = 1 if totasshh >= 1000 & totasshh < .
label variable dtotasshh1000 "Dummy totasshh >= 1000 euro"
summ dtotasshh1000 totasshh
summ totasshh if dtotasshh1000 == 0
summ totasshh if dtotasshh1000 == 1



* Generate fraction of total assets invested in financial assets, real assets (non-housing), housing and private business.
*
* Note: only for those people with positive total asset amounts, otherwise missing. 
* 
gen wtfinass       = finass/totass      if dtotasspos == 1
gen wtprivbus      = privbus/totass      if dtotasspos == 1
gen wthousing      = realass_housing/totass  if dtotasspos == 1 
gen wtrealother    = realass_other/totass  if dtotasspos == 1 
label variable wtfinass "Financial assets weight in total assets"
label variable wtprivbus "Private business weight in total assets"
label variable wthousing "Housing weight in total assets"
label variable wtrealother "Other real assets weight in total assets"
gen wtchecksum = wtfinass + wtprivbus + wthousing + wtrealother
sum wtfinass wtprivbus wthousing wtrealother wtchecksum 
sum wtfinass wtprivbus wthousing wtrealother wtchecksum if dhouse_ms == 0
sum wtfinass wtprivbus wthousing wtrealother wtchecksum if dhouse_ms == 1

gen wtfinasshh       = finasshh/totasshh      if dtotasshhpos == 1
gen wtprivbushh      = privbushh/totasshh      if dtotasshhpos == 1
gen wthousinghh      = realasshh_housing/totasshh  if dtotasshhpos == 1 
gen wtrealotherhh    = realasshh_other/totasshh  if dtotasshhpos == 1 
label variable wtfinasshh "Financial assets weight in total household assets"
label variable wtprivbushh "Private business weight in total household assets"
label variable wthousinghh "Housing weight in total household assets"
label variable wtrealotherhh "Other real assets weight in total household assets"
gen wtchecksumhh = wtfinasshh + wtprivbushh + wthousinghh + wtrealotherhh
sum wtfinasshh wtprivbushh wthousinghh wtrealotherhh wtchecksumhh 



* Unsecured debt: s1b s2b s3b s4b s5b s6b s7b s8b x1b
*
* Note: This is basically all non-mortgage debt
*
summ s1b s2b s3b s4b s5b s6b s7b s8b x1b
gen debt_unsecured =  s1b + s2b + s3b + s4b + s5b + s6b + s7b + s8b + x1b
label variable debt_unsecured "Unsecured debt amount in euros"
summ debt_unsecured 
by nohhold: egen debthh_unsecured  =total(debt_unsecured)     
label variable debthh_unsecured  "Unsecured household debt amount in euros"
summ debt_unsecured debthh_unsecured 


* Mortgage debt: b19hyb b26hyb b27hyb 
*
* b19hyb: mortgage on real estate not being used as own accomodation (not 1st or 2nd residence, see below)
* b26hyb: mortgage on 1st residence
* b27hyb: mortgage on 2nd residence
*
* Note: Note b26hyb and b27hyb are from questionnaire Accommodation and Mortgages, and have lower number of observations
*
summ b19hyb b26hyb b27hyb 
gen b26hyb_nm = b26hyb
replace b26hyb_nm = 0 if b26hyb == .
gen b27hyb_nm = b27hyb
replace b27hyb_nm = 0 if b27hyb == .
gen debt_housing = b19hyb + b26hyb_nm + b27hyb_nm
label variable debt_housing "Mortgage debt amount in euros"
summ debt_housing
summ debt_housing b19hyb b26hyb b27hyb 
summ debt_housing b19hyb b26hyb b27hyb if dhouse_ms == 0
summ debt_housing b19hyb b26hyb b27hyb if dhouse_ms == 1
by nohhold: egen debthh_housing  =total(debt_housing)     
label variable debthh_housing "Household mortgage debt amount in euros"
summ debt_housing debthh_housing 


* Total debt
*
gen totdebt = debt_unsecured  + debt_housing
label variable totdebt "Total debt in euros"
summ totdebt debt_unsecured debt_housing 
summ totdebt debt_unsecured debt_housing if dhouse_ms == 0
summ totdebt debt_unsecured debt_housing if dhouse_ms == 1

by nohhold: egen totdebthh =total(totdebt) 
label variable totdebthh "Total household debt in euros"
gen check_totdebthh = debthh_unsecured  + debthh_housing
summ totdebt totdebthh check_totdebthh



* Total net wealth
*
* Note: negative checking balances 
* 
gen netwealth = totass - totdebt
label variable netwealth "Total net wealth in euros (totass - totdebt)"
summ netwealth totass finass realass_other realass_housing totdebt debt_unsecured debt_housing 
summ netwealth totass finass realass_other realass_housing totdebt debt_unsecured debt_housing  if dhouse_ms == 0
summ netwealth totass finass realass_other realass_housing totdebt debt_unsecured debt_housing  if dhouse_ms == 1

gen check_netwealth = (b1bpos + b2b + b3b + b4b + b6b + b7b + b8b + b19vzb + b26vzb_nm + b27vzb_nm + b13b + b11b + b12b + b14b + b15b + b16b + b17b + b18b + b24b + b25b) ///
                   + (b20b + b21b + b22b + b23b) + (b19ogb + b26ogb_nm + b27ogb_nm) + (b28b + b29b + b30b ) ///
                   - (s1b + s2b + s3b + s4b + s5b + s6b + s7b + s8b + x1b) -  (b19hyb + b26hyb_nm + b27hyb_nm)
summ netwealth check_netwealth				   

by nohhold: egen netwealthhh =total(netwealth) 
label variable netwealthhh "Total household net wealth in euros (totasshh - totdebthh)"
gen check_netwealthhh = totasshh - totdebthh
summ netwealth netwealthhh check_netwealthhh



* Create one more variable for the number of stocks
*
tab b14a
gen nstocks = b14a
label variable nstocks "Number of stocks (DHS-2018, wealth as of Dec 2017)"
summ nstocks if dstocks == 0
summ nstocks if dstocks == 1


***********************************************************************
****** Save the processed wealth file under a new name
***********************************************************************

sort noid
save wealth2018_decoded.dta, replace


***********************************************************************
****** Save a short version of the processed wealth file 
***********************************************************************

* Save a short version of the new wealth file that only includes the variables
* that are used to create the table in the article 
*
keep nohhold nomem noid year savings dsavpos savingshh dsavhhpos insurance0 insurance insurancehh insurance0hh mutualfunds dmutualfunds mutualfundshh dmutualfundshh bonds dbonds dbondshh stocks dstocks stockshh dstockshh options doptions optionshh doptionshh finother dfinother finotherhh dfinotherhh bondsorfunds dbondsorfunds bondsorfundshh dbondsorfundshh equity dequity equityhh dequityhh finass dfinasspos dfinass1000 finasshh dfinasshhpos dfinasshh1000 wsavings winsurance wbonds wmutualfunds wstocks woptions wfinother wequity wbondsorfunds wsavingshh winsurancehh wbondshh wmutualfundshh wstockshh woptionshh wfinotherhh wequityhh wbondsorfundshh realass_other realasshh_other realass_housing realasshh_housing dhouse_ms privbus privbushh dprivbus dprivbushh totass totasshh dtotasspos dtotasshhpos dtotass1000 dtotasshh1000 wtfinass wtprivbus wthousing wtrealother wtfinasshh wtprivbushh wthousinghh wtrealotherhh debt_unsecured debthh_unsecured debt_housing debthh_housing totdebt totdebthh netwealth nstocks
sort noid
save wealth2018_summary.dta, replace




